package com.szholly.pro.watergas_server.ps.android.controller;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.IWorkspace;
import com.szholly.data.general.IWorkspaceFactory;
import com.szholly.data.general.RowBase;
import com.szholly.utils.spring.SpringBeanFactory;

import java.util.ArrayList;
import java.util.List;

public class DataModel {
	public List<RowBase> CalcPanelDiffFromDB(String project_no){
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		
		String getProjectItemNosSql = "select ITEM_NO from project_item where project_no=@project_no";
		GlobalParameter paras = new GlobalParameter("project_no", project_no);
		List<RowBase> itemNos =  pWorkspace.getEntityList(RowBase.class,getProjectItemNosSql,new GlobalParameter[]{paras});
		
		String itemNoWhereStr="";
		List<GlobalParameter> paraList = new ArrayList<GlobalParameter>();
		for(int i =0;i<itemNos.size();i++){
			String itemNo = itemNos.get(i).getValue("ITEM_NO")+"";
			itemNoWhereStr += ",@ITEM_NO"+i;
			paraList.add(new GlobalParameter("@ITEM_NO"+i, itemNo));
		}
		itemNoWhereStr = "(" + itemNoWhereStr.replaceFirst(",", "") + ")";
		
		String getWellNumSql = " select count(*) from attr_review where project_item_no in "+itemNoWhereStr;
		String wellNumStr = pWorkspace.getExecuteScalar(getWellNumSql, paraList.toArray(new GlobalParameter[paraList.size()]))+"";
		
		if(wellNumStr.equals("0")){
			return null;
		}
		
		String deleteSql = " DELETE FROM ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO=@PROJECT_NO AND DIFF_TYPE='panel' ";
		pWorkspace.executeSql(deleteSql, new GlobalParameter[]{new GlobalParameter("PROJECT_NO", project_no)});
		
		String sql = "select 'panel' DIFF_TYPE,'Δd≤0.16' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) / "+wellNumStr +",5) * 100 SCALE from attr_review a where project_item_no in "+itemNoWhereStr+" and a.plane_diff <= 0.16 \n" +
						"union all\n" + 
						"select 'panel' DIFF_TYPE,'0.16<Δd≤1.0 ' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a where project_item_no in "+itemNoWhereStr+" and  0.16 < a.plane_diff and a.plane_diff <= 1\n" + 
						"union all\n" + 
						"select 'panel' DIFF_TYPE,'1.0<Δd≤2.0' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a where project_item_no in "+itemNoWhereStr+" and  1 < a.plane_diff and a.plane_diff  <= 2\n" + 
						"union all\n" + 
						"select 'panel' DIFF_TYPE,'2.0<Δd≤5.0' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a where project_item_no in "+itemNoWhereStr+" and  2 < a.plane_diff and a.plane_diff  <= 5\n" + 
						"union all\n" + 
						"select 'panel' DIFF_TYPE,'Δd>5.0' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a where project_item_no in "+itemNoWhereStr+" and  a.plane_diff > 5";
		
		List<RowBase> list =  pWorkspace.getEntityList(RowBase.class,sql,paraList.toArray(new GlobalParameter[paraList.size()]));
		for(RowBase row : list){
			row.setIsNew(true);
		}
		//pWorkspace.close();
		return list;
	}	

	public List<RowBase> CalcHeightDiffFromDB(String project_no){
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		
		String getProjectItemNosSql = "select ITEM_NO from project_item where project_no=@project_no";
		GlobalParameter paras = new GlobalParameter("project_no", project_no);
		List<RowBase> itemNos =  pWorkspace.getEntityList(RowBase.class,getProjectItemNosSql,new GlobalParameter[]{paras});
		
		String itemNoWhereStr="";
		List<GlobalParameter> paraList = new ArrayList<GlobalParameter>();
		for(int i =0;i<itemNos.size();i++){
			String itemNo = itemNos.get(i).getValue("ITEM_NO")+"";
			itemNoWhereStr += ",@ITEM_NO"+i;
			paraList.add(new GlobalParameter("@ITEM_NO"+i, itemNo));
		}
		itemNoWhereStr = "(" + itemNoWhereStr.replaceFirst(",", "") + ")";
		
		String getWellNumSql = " select count(*) from attr_review where project_item_no in "+itemNoWhereStr;
		String wellNumStr = pWorkspace.getExecuteScalar(getWellNumSql, paraList.toArray(new GlobalParameter[paraList.size()]))+"";
		
		if(wellNumStr.equals("0")){
			return null;
		}
		
		String deleteSql = " DELETE FROM ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO=@PROJECT_NO AND DIFF_TYPE='height' ";
		pWorkspace.executeSql(deleteSql, new GlobalParameter[]{new GlobalParameter("PROJECT_NO", project_no)});
		
		String sql = "select 'height' DIFF_TYPE,'Δh≤0.14 ' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) / "+wellNumStr +",5) * 100 SCALE from attr_review a "
				+ "	where project_item_no in "+itemNoWhereStr+" and a.height_diff <= 0.14 \n" +
						"union all\n" + 
						"select 'height' DIFF_TYPE,'0.14<Δh≤0.3  ' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a "
								+ "where project_item_no in "+itemNoWhereStr+" and  0.14 < a.height_diff and a.plane_diff <= 0.3\n" + 
						"union all\n" + 
						"select 'height' DIFF_TYPE,'0.3<Δh≤0.5' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a "
								+ "where project_item_no in "+itemNoWhereStr+" and  0.3 < a.height_diff and a.plane_diff  <= 0.5\n" + 
						"union all\n" + 
						"select 'height' DIFF_TYPE,'0.5<Δh≤1.0 ' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a "
								+ "where project_item_no in "+itemNoWhereStr+" and  0.5 < a.height_diff and a.plane_diff  <= 1\n" + 
						"union all\n" + 
						"select 'height' DIFF_TYPE,'Δh>1.0 ' STATISTICS_BOUND,'"+project_no+"' PROJECT_NO,count(*) WELL_NUM,round(count(*) /  "+wellNumStr +",5) * 100  SCALE from attr_review a "
								+ "where project_item_no in "+itemNoWhereStr+" and  a.height_diff > 1";
		
		List<RowBase> list =  pWorkspace.getEntityList(RowBase.class,sql,paraList.toArray(new GlobalParameter[paraList.size()]));
		for(RowBase row : list){
			row.setIsNew(true);
		}
		//pWorkspace.close();
		return list;
	}
}
